package com.spt.ws.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.spt.core.Property;
import com.spt.fjj.service.models.common.AnnotationCommonService;
import com.spt.ws.dao.IClientDao;

@Service
public class ClientDaoImpl extends AnnotationCommonService implements IClientDao {
	private static Logger log = LogManager.getLogger("com.spt.ws.client");
	@Transactional
	@Override
	public List<Map<String, Object>> getYJSM() throws Exception {
		String sbid = Property.getProperty("fjj.sbid");
		String cjdm = Property.getProperty("fjj.cjdm");
		String sql_clear = "update BGFJJ_TRACE set csbz = '8' where visChar(yjtm) = 0";
		this._update(sql_clear);
		String sql_setBZ = "update BGFJJ_TRACE set csbz = 5 "
				+ " where csbz = '0' and isnumeric(car)=1 and isnumeric(gjthm) = 1 and isnumeric(weight) = 1 " //and length(smsj)=19
				+ " and rownum < 300 "
				+ " and yjtm <> '  ' and length(smqh) = 6 and tj is not null" ;  //guangzhou
		//+ "from BGFJJ_TRACE where csbz = '0' and (length(yjtm) = 13 or length(yjtm) = 30) and yldm <> '00000000'";  //suzhou
		this._update(sql_setBZ);
		//this._update("update BGFJJ_TRACE set csbz = 5 where csbz = '0' and isnumeric(car)=1 and isnumeric(gjthm) = 1 and isnumeric(weight) = 1 and rownum < 3000  and yjtm <> '  ' and length(smqh) = 6 and tj is not null");
		/*String sql = "select '"+ sbid + "' as sbid,"
				//+ "replace(yjtm,chr(0),'x') as yjtm"  //2016-04-15 修改上传yjtm规则
				+ " case length(yjtm) when 13 then yjtm when 30 then yjtm else '*' end as yjtm " //2016-04-15 修改上传yjtm规则 
				+ ",smczy,"
				+ "to_char(to_date(smsj,'yyyy/mm/dd hh24:mi:ss'),'YYYYMMDDHH24MISS') as smsj,"
				+ "case  gjfs when 'AT' then '3' when 'MB' then '2' else '1' end as smly ,"
				+ "gjthm as smth,car as xcbm,"
				+ "'" + cjdm + "' as cjdm,"
				//+ " case when  weight < 99990 then to_char(ROUND(weight/10,0)) else '9999' end as sjzl,"
				+ " case when to_number(weight) < 9999 then weight else '9999' end as sjzl, "
				+ "nvl(trim(length),'0') as length,nvl(trim(width),'0') as width,nvl(trim(height),'0') as height,id,car1 as XCBH1,smqh,"
				//+ "nvl(trim(length),'0') * nvl(trim(width),'0') * nvl(trim(height),'0') as tj"
				+ " tj "
				//+ "from BGFJJ_TRACE where csbz = '0' and (length(yjtm) = 13 or length(yjtm) = 30) and isnumeric(car)=1 and isnumeric(gjthm) = 1 and isnumeric(weight) = 1";  //guangzhou
				+ " from BGFJJ_TRACE where csbz = '0' and isnumeric(car)=1 and isnumeric(gjthm) = 1 and isnumeric(weight) = 1 " //and length(smsj)=19
				+ " and rownum < 3000"
				+ " and yjtm <> '  ' and length(smqh) = 6 and tj is not null" ;  //guangzhou
				//+ "from BGFJJ_TRACE where csbz = '0' and (length(yjtm) = 13 or length(yjtm) = 30) and yldm <> '00000000'";  //suzhou*/
		String sql = "select '"+ sbid + "' as sbid,"
				//+ "replace(yjtm,chr(0),'x') as yjtm"  //2016-04-15 修改上传yjtm规则
				+ " case length(yjtm) when 13 then yjtm when 30 then yjtm else '*' end as yjtm " //2016-04-15 修改上传yjtm规则
				+ ",smczy,"
				+ "to_char(to_date(smsj,'yyyy/mm/dd hh24:mi:ss'),'YYYYMMDDHH24MISS') as smsj,"
				+ "case  gjfs when 'AT' then '3' when 'MB' then '2' else '1' end as smly ,"
				+ "gjthm as smth,car as xcbm,"
				+ "'" + cjdm + "' as cjdm,"
				+ " case when to_number(weight) < 9999 then weight else '9999' end as sjzl, "
				+ "nvl(trim(length),'0') as length,nvl(trim(width),'0') as width,nvl(trim(height),'0') as height,id,car1 as XCBH1,smqh,"
				+ " tj "
				+ " from BGFJJ_TRACE where csbz = '5' " ;

		List<Map<String,Object>> li = this._queryMultex(sql);
		return li;
	}
	@Transactional
	@Override
	public void setYJSMFlag(String flag) throws Exception {
		//String ids = "'" + idList.replace(",", "','") + "'";
		/*String sql = "update BGFJJ_TRACE t set t.csbz = '"
				+ flag
				+ "' where id in (" + idList + ")";*/
		//this._update(sql);
		this._update("update BGFJJ_TRACE t set t.csbz = ? where csbz = 5", flag);

	}
	@Transactional
	@Override
	public void setYJSMFlag(String flag, String id) throws Exception {
		this._update("update BGFJJ_TRACE t set t.csbz = ? where csbz = 5 and id = ?", flag, id);
	}

	@Transactional
	@Override
	public List<Map<String, Object>> getYJLG() throws Exception {
		//String sql_clear = "update BGFJJ_FFYJXXB set scbz = '8' where visChar(yjtm) = 0";
		//this._update(sql_clear);
		String sql_setBZ = "update BGFJJ_FFYJXXB set scbz = 5 "
				+ " where scbz = '0' and sbid is not null and lgsj is not null and cjdm is not null"
				+ " and WLGKHM is not null  "
				+ " and rownum < 300";//guangzhou
		      //+ "from BGFJJ_FFYJXXB where scbz = '0' and yldm <> '00000000'";//suzhou
		this._update(sql_setBZ);
		/*String sql = "select SBID,yjtm,to_char(SJZL) as SJZL,to_char(GJTH) as GJTH,GBCZY,to_char(XCBH) as XCBH,"
				+ "to_char(to_date(LGSJ,'yyyy/mm/dd hh24:mi:ss'),'YYYYMMDDHH24MISS') as LGSJ,"
				+ "YLDM,LXGK,YJZL,to_char(WLGKHM) as WLGKHM,CJDM,BZ,"
				+ "nvl(trim(length),'0') as length,nvl(trim(width),'0') as width,nvl(trim(height),'0') as height,id,XCBH1,"
				+ "obr, "
				//+ "nvl(trim(length),'0') * nvl(trim(width),'0') * nvl(trim(height),'0') as tj "
				+ " tj,XJZ,BXLGBZ,FAID "
				+ "from BGFJJ_FFYJXXB where scbz = '0' and sbid is not null and lgsj is not null and cjdm is not null"
				+ " and WLGKHM is not null  "
				+ " and rownum < 1000";//guangzhou
				//+ "from BGFJJ_FFYJXXB where scbz = '0' and yldm <> '00000000'";//suzhou*/
		String sql = "select SBID,yjtm,to_char(SJZL) as SJZL,to_char(GJTH) as GJTH,GBCZY,to_char(XCBH) as XCBH,"
				+ "to_char(to_date(LGSJ,'yyyy/mm/dd hh24:mi:ss'),'YYYYMMDDHH24MISS') as LGSJ,"
				+ "YLDM,LXGK,YJZL,to_char(WLGKHM) as WLGKHM,CJDM,BZ,"
				+ "nvl(trim(length),'0') as length,nvl(trim(width),'0') as width,nvl(trim(height),'0') as height,id,XCBH1,"
				+ "obr, "
				//+ "nvl(trim(length),'0') * nvl(trim(width),'0') * nvl(trim(height),'0') as tj "
				+ " tj,XJZ,BXLGBZ,FAID "
				+ "from BGFJJ_FFYJXXB where scbz = '5' ";//guangzhou
		List<Map<String,Object>> li = this._queryMultex(sql);
		return li;
	}
	
	@Transactional
	@Override
	public void setYJLGFlag(String flag) throws Exception {
		//String ids = "'" + idList.replace(",", "','") + "'";
		/*String sql = "update BGFJJ_FFYJXXB t set t.scbz = '"
				+ flag
				+ "' where id in (" + idList + ")";
		this._update(sql);*/
		this._update("update BGFJJ_FFYJXXB t set t.scbz = ? where scbz = 5",flag);

	}

	@Override
	public void setYJLGFlag(String flag, String id) throws Exception {
		this._update("update BGFJJ_FFYJXXB t set t.scbz = ? where scbz = 5 and id = ?",flag, id);
	}

	@Transactional
	@Override
	public List<Map<String, Object>> getBFJZT() throws Exception {
		String setTranStatus = "update BGFJJ_ZTXXB set scbz = '5' where  ROWNUM < 300 and scbz = 0";
		this._update(setTranStatus);
		List<Map<String, Object>> li = this._queryMultex("select SBID,KJSJ,GJSJ,to_char(YXSJ) YXSJ,BFJSX,BFJYXXL from BGFJJ_ZTXXB where scbz = '5'");
		
		return li;
	}
	@Transactional
	@Override
	public void setBFJZTFlag(String flag) throws Exception {
		String sql = "update BGFJJ_ZTXXB t set t.scbz = ? where scbz = '5'";
		this._update(sql,flag);
	}
	@Transactional
	@Override
	public List<Map<String, Object>> getGKZT() throws Exception {
	    String setTranStatus = "update BGFJJ_GKZTXXB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,to_char(WLGKH) WLGKH,GKZT,GKSJ,GKFGSX from BGFJJ_GKZTXXB t  where scbz = '5'");
        
        return li;
	}
	@Transactional
	@Override
	public void setGKZTFlag(String flag) throws Exception {
		String sql = "update BGFJJ_GKZTXXB set scbz = ? where scbz = '5'";
		this._update(sql,flag);
	}
	@Transactional
	@Override
	public List<Map<String,Object>> getGJCZT() throws Exception {
	    String setTranStatus = "update BGFJJ_GJCZTXXB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,CJDM,TO_CHAR(BFJXCBH) BFJXCBH,BFJXCZT,BFJXCZTSJ from BGFJJ_GJCZTXXB t where scbz = '5'");
        
		return li;
	}
	@Transactional
	@Override
	public void setGJCZTFlag(String flag) throws Exception{
	    String sql = "update BGFJJ_GJCZTXXB set scbz = ? where scbz = '5'";
        this._update(sql,flag);
	}
	@Transactional
	@Override
	public List<Map<String, Object>> getOBRZT() throws Exception {
	    String setTranStatus = "update BGFJJ_OBRZTXXB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select BFJID,CJDM,OBR,ZT,SCSJ from BGFJJ_OBRZTXXB t where scbz = '5'");
        
		return li;
	}
	@Transactional
	@Override
	public void setOBRZTFlag(String flag) throws Exception {
	    String sql = "update BGFJJ_OBRZTXXB set scbz = ? where scbz = '5'";
        this._update(sql,flag);
	}
	
	@Transactional
	@Override
	public List<Map<String, Object>> getGJTZT() throws Exception {
	    String setTranStatus = "update BGFJJ_GJTZTXXB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,to_char(GJTHM) GJTHM,GJTZT,to_char(to_date(gjtkgsj,'yyyy/mm/dd hh24:mi:ss'),'yyyymmddhh24miss') gjtkgsj,GJTSX,GJFS,to_char(GBJS) GBJS from BGFJJ_GJTZTXXB where scbz = '5'");
        
        return li;
	}
	@Transactional
	@Override
	public void setGJTZTFlag(String flag) throws Exception {
		String sql = "update BGFJJ_GJTZTXXB set scbz = ? where scbz = '5'";
		this._update(sql, flag);
	}
	@Transactional
	@Override
	public List<Map<String, Object>> getSRYY() throws Exception {
		/*String sql = "select SBID,CJDM,SRGKJRSJ,NVL(YJTM,' ') as yjtm,ID,to_char(WLGKH) as WLGKH,SRYY "
				+ "from BGFJJ_SRGKZTXXB  where scbz = '0' ";*/
		String sql_clear = "update BGFJJ_SRGKZTXXB set csbz = '8' where visChar(yjtm) = 0";
		this._update(sql_clear);
		Map<String,String> map = this.getParm("sryy");
		StringBuffer sb = new StringBuffer("select SBID,CJDM,"
				+ "to_char(to_date(SRGKJRSJ,'yyyy/mm/dd hh24:mi:ss'),'YYYYMMDDHH24MISS') as SRGKJRSJ,"
				+ "case when substr(NVL(replace(yjtm,chr(0),'x'),' '),length(NVL(replace(yjtm,chr(0),'x'),' ')),1)=':' "
				+ "then substr(yjtm,0,length(yjtm)-1) else NVL(yjtm,' ') end as yjtm,"
				+ "ID,to_char(WLGKH) as WLGKH, case ");
		String el = null;
		for(String key:map.keySet()){
			String val = map.get(key);
			if("other".equalsIgnoreCase(val)) {
				el = " else '" + "5' ";
				continue;
			}
			sb.append(" when SRYY in ('").append(val.replace(",", "','")).append("') then '").append(key).append("' ");
		}
		if(el != null) sb.append(el);
		sb.append("end as SRYY ");
		sb.append(" from BGFJJ_SRGKZTXXB  where csbz = '0'");
		List<Map<String,Object>> li = this._queryMultex(sb.toString());
		return li;
	}
	@Transactional
	@Override
	public void setSRYYFlag(String idList,String flag) throws Exception {
		String sql = "update BGFJJ_SRGKZTXXB t set t.csbz = '"
				+ flag
				+ "' where id in (" + idList + ")";
		this._update(sql);
	}
	@Transactional
	@Override
	public List<Map<String, Object>> getSRGKZT() throws Exception {
	    String setTranStatus = "update BGFJJ_SRGKZTXXB set csbz = '5' where ROWNUM < 300  and csbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,CJDM,to_char(to_date(SRGKJRSJ,'yyyy/mm/dd hh24:mi:ss'),"
                + "'yyyymmddhh24miss') SRGKJRSJ,YJTM,to_char(WLGKH) WLGKH,"
                + "case SRYY when 'DE' then '1' when 'ID' then '2' when 'NR' then '3' when 'MR' then '4' else '5' end sryy "
                + " from BGFJJ_SRGKZTXXB t where csbz = '5'");
        
        return li;
	}
	@Transactional
	@Override
	public void setSRGKZTFlag(String flag) throws Exception {
		String sql = "update BGFJJ_SRGKZTXXB t set t.csbz = ? where csbz='5'";
		this._update(sql, flag);
	}
	@Transactional
	private Map<String,String> getParm(String classid) throws Exception{
		String sql = "select sub_class_id,param_value from ST_SYS_PARAM where class_id = '" + classid + "'";
		List<Map<String, Object>> li = this._queryMultex(sql);
		if(li==null || li.size() == 0) return null;
		Map<String,String> parm = new HashMap<String,String>();
		for(Map<String, Object> map : li){
			parm.put((String)map.get("SUB_CLASS_ID"), (String)map.get("PARAM_VALUE"));
		}
		return parm;
	}
	
	@Override
	public List<Map<String, Object>> getQGXX() throws Exception {
		//String sql1 = "select FFQDID,time from BGFJJ_QGXXB where bz = 0";
		//List<Map<String,Object>> li = this._queryMultex(sql1);
		String sql2 = "select CJDM,c.faid,lxgk,to_char(WLGKHM) as WLGKHM,time as ffsj,"
				+ "to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yy') as shortyear ,"
				+ "nvl(YJTM,'0000000000000') as yjtm,yjzl,to_char(SJZL) as SJZL,"
				+ "to_char(GJTH) as GJTH,GBCZY,to_char(XCBH) as XCBH,"
				+ "nvl(trim(length),'0') as length,nvl(trim(width),'0') as width,nvl(trim(height),'0') as height, "
				+ " gjth,gbczy,xcbh,lgsj b.FFQDID "
				+ "from BGFJJ_FFYJXXB a "
				+ " left join BGFJJ_QGXXB b on a.FFQDID = b.FFQDID "
				+ " left join bgfjj_tdqfab c on a.sbid = c.sbid and a.cjdm = c.cjdm "
				+ "where scbz = '0' and b.bz = '0' order by b.FFQDID";
		List<Map<String,Object>> li = this._queryMultex(sql2);
		List<Map<String,Object>> lirtn = new ArrayList<Map<String,Object>>();
		
		Map<String,Object> tmpMap = null;
		List<Map<String,Object>> tmpRows = null;
		Map<String,Object> tmpMapRow = null;
		String ffqdid = "";
		for (Map<String,Object> map : li){
			String tmpffqdid = (String) map.get("FFQDID");
			if(!ffqdid.equals(tmpffqdid)){
				if(tmpMap != null) {
					if(tmpRows!=null) tmpMap.put("ROWS", tmpRows);
					lirtn.add(tmpMap);
				}
				tmpMap = new HashMap<String,Object>();
				tmpRows = new ArrayList<Map<String,Object>>();
				tmpMap.put("CJDM", map.get("CJDM"));
				String lsh = this._querySingle("select seq_qgxx.netVal from dual");
				tmpMap.put("PCH", (String)map.get("SHORTYEAR") + map.get("CJDM") + "FJ" + lsh);
				tmpMap.put("FAID", map.get("FAID"));
				tmpMap.put("WLGKH", map.get("WLGKHM"));
				tmpMap.put("FFSJ", map.get("FFSJ"));
				tmpMap.put("FFQDID", map.get("FFQDID"));
			}
			tmpMapRow = new HashMap<String,Object>();
			tmpMapRow.put("YJTM", "YJTM");
			tmpMapRow.put("YJZL", "YJZL");
			tmpMapRow.put("SJZL", "SJZL");
			tmpMapRow.put("LENGTH", "LENGTH");
			tmpMapRow.put("WIDTH", "WIDTH");
			tmpMapRow.put("HEIGHT", "HEIGHT");
			tmpMapRow.put("GJTH", "GJTH");
			tmpMapRow.put("GBCZY", "GBCZY");
			tmpMapRow.put("XCBH", "XCBH");
			tmpMapRow.put("LGSJ", "LGSJ");
			tmpRows.add(tmpMapRow);
		}
		if(tmpMap != null) {
			if(tmpRows!=null) tmpMap.put("ROWS", tmpRows);
			lirtn.add(tmpMap);
		}
		return lirtn;
	}
	@Override
	public void setQGXX(String ffqdid) throws Exception {
		String sql = "update BGFJJ_QGXXB set bz = '1' where ffqdid = ?";
		this._update(sql,ffqdid);
		
	}
	@Override
	public List<Map<String, Object>> testDB() throws Exception {
		/*String sql = "insert into testdb values(sysdate)";
		this._insert(sql);*/
		return this._queryMultex("select 'a' dd from dual");
	}
	
	@Override
	public void saveTransferStatus(String method, Integer delayTime) throws Exception {
		String sql = "update T_MANAGE_LIST set delaytime = ?,lastActDt = to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') where dealfunction = ? ";
		this._update(sql, delayTime, method);
		
	}
    @Override
    public List<Map<String, Object>> getQGFF() throws Exception {
        String sbid = Property.getProperty("fjj.sbid");
		log.debug("sbid ========"+ sbid);
        String setTranStatus = "update BGFJJ_TZBXXB set scbz = '5' where ROWNUM< 300 and scbz =0";
        this._update(setTranStatus);
        String sqlhead = "select CJDM,PCH,to_char(FAID) FAID,LXGK,LTRIM(wlgkh,'0') WLGKH,to_char(to_date(FFSJ,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss') ffsj " +
				" ,'" + sbid + "' SBID " +
				"from BGFJJ_TZBXXB t where scbz = '5'";
        log.debug(sqlhead);
        String sqlbody = "select YJTM,YJZL,to_char(SJZL) SJZL,to_char(LENGTH) LENGTH,"
                + "to_char(WIDTH) WIDTH,to_char(HEIGHT) HEIGHT,to_char(GJTH) GJTH,"
                + "GBCZY,to_char(XCBH) XCBH,to_char(to_date(LGSJ,'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss') LGSJ,to_char(XCBH1) XCBH1,BXLGBZ "
                + "from BGFJJ_TZBSJXXB t "
                + " where pch = ?";
        List<Map<String,Object>> li = this._queryMultex(sqlhead);
        if(li == null || li.size() == 0) return null;
        for(Map<String,Object> map : li){
            List<Map<String,Object>> libody = this._queryMultex(sqlbody, map.get("PCH"));
            if(libody==null || libody.size() ==0) continue;
            map.put("maillist", libody);
            //map.put("SBID", sbid);
        } 
        return li;
    }
    @Override
    public void updateQGFF(Map<String, Object> row,String flag) throws Exception {
        String sqlhead = "update BGFJJ_TZBXXB t set t.scbz = ? where pch=?";
        //String sqlbody = "update BGFJJ_TZBSJXXB t set t.scbz = ? where ffqdid =?";
        this._update(sqlhead,flag,row.get("PCH"));
        //this._update(sqlbody,flag,row.get("ID"));
    }
    @Override
    public List<Map<String, Object>> getHDLTJ() throws Exception {
        String setTranStatus = "update BGFJJ_HDLTJB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,SCSJ,to_char(YXSJ) YXSJ,HDL from BGFJJ_HDLTJB t where scbz = '5'");
        
        return li;
    }
    @Override
    public void setHDLTJflag(String flag) throws Exception {
        String sql = "update BGFJJ_HDLTJB t set t.scbz = ? where scbz='5'";
        this._update(sql, flag);
    }
    @Override
    public List<Map<String, Object>> getMPLTJ() throws Exception {
        String setTranStatus = "update BGFJJ_MPLTJB set scbz = '5' where ROWNUM < 300  and scbz = 0";
        this._update(setTranStatus);
        List<Map<String, Object>> li = this._queryMultex("select SBID,SCSJ,to_char(YXSJ) YXSJ,to_char(KXCS) KXCS,to_char(SJXCS) SJXCS from BGFJJ_MPLTJB t where scbz = '5'");
        
        return li;
    }
    @Override
    public void setMPLTJflag(String flag) throws Exception {
        String sql = "update BGFJJ_MPLTJB t set t.scbz = ? where scbz='5'";
        this._update(sql, flag);
    }
}
